using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using PropertyManagement.Core.Model;

namespace PropertyManagement.Core.DataAccess {
        public static partial class PropertyManagementFeeBillDataAccess {
             
            public static IEnumerable<PropertyManagementFeeBill> Search(int? houseID,DateTime? start,DateTime? end,BillType? billType,bool? isPaid) {
                var sql = "select * from dbo.PropertyManagementFeeBill where 1=1 ";
                var paramList = new List<SqlParameter> {
                    
                };
                if (houseID != null) {
                    sql += "and HouseId = @houseid";
                    paramList.Add(new SqlParameter("houseid", houseID.Value));
                }
                if (start != null) {
                    sql += " and EffectiveDate >= @effectivedate1";
                    paramList.Add(new SqlParameter("effectivedate1",start.Value));
                }
                if (end != null) {
                    sql += " and EffectiveDate <= @effectivedate2";
                    paramList.Add(new SqlParameter("effectivedate2", end.Value));
                }
                if (billType != null) {
                    sql += " and BillType <= @billtype";
                    paramList.Add(new SqlParameter("billtype", billType.Value));
                }
                if (isPaid != null) {
                    sql += " and IsCompletePaid = @iscompletepaid";
                    paramList.Add(new SqlParameter("iscompletepaid", isPaid.Value));
                }
                var list = new List<PropertyManagementFeeBill>( );
                using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, 
                    CommandType.Text, sql,paramList.ToArray())) {
                    while (iReader.Read( )) {
                        list.Add(CreationPropertyManagementFeeBillByIReader(iReader));
                    };
                }
                return list;

            } 



    }
}